package context.dao;

import context.model.FoodClass;
import context.model.FoodSchool;
import context.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class FoodschoolRepository {
    private final String SELECT_BY_NAME = "select * from foodschool where className=?";
    private final String SELECT_BY_ID = "select * from joinschool where userid=?";
    private final String SELECT_BY_FSID = "select * from foodschool where foodSchoolID=?";
    private final String INSERT_USER_INFORMATION = "insert into foodschool(userid,className,classDes,classImg) values(?,?,?,?)";
    private final String COUNT = "select * from foodschool where userid=?";
    private JdbcTemplate jdbcTemplate;

    private Connection con;
    @Autowired          //自动装配
    public FoodschoolRepository(JdbcTemplate jdbcTemplate) throws SQLException {
        this.jdbcTemplate = jdbcTemplate;
        con = jdbcTemplate.getDataSource().getConnection();
    }

    private FoodSchool mapRow(ResultSet resultSet, int i) throws SQLException {
        FoodSchool result = new FoodSchool();
        result.setUserid(resultSet.getLong("userid"));
        result.setClassName(resultSet.getString("className"));
        result.setClassDes(resultSet.getString("classDes"));
        result.setClassImg(resultSet.getString("classImg"));
        return result;
    }

    //增
    public long insertClassInformation(final long userid, final String className, final String classDes, final String classImg) {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(
                new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                        PreparedStatement ps = jdbcTemplate.getDataSource()
                                .getConnection().prepareStatement(INSERT_USER_INFORMATION, new String[]{"userid", "className", "classDes", "classImg"});
                        ps.setLong(1, userid);
                        ps.setString(2, className);
                        ps.setString(3, classDes);
                        ps.setString(4, classImg);
                        return ps;
                    }
                }, keyHolder);
        System.out.println("自动插入id============================" + keyHolder.getKey().intValue());
        return keyHolder.getKey().longValue();
    }

    //删
    private static final String sql = "delete  from foodschool where foodSchoolID = ?";

    public void deleteClass(long foodschoolid) {
        Object[] obj = new Object[]{foodschoolid};
        int i = jdbcTemplate.update(sql, obj);

        if (i > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
    }


    //查用户是否选择课程
    public List<FoodSchool> findClassByJoinedUserid(long id) {

        List<FoodSchool> classList = new ArrayList<>();        //构建一个list,保存课程

        try {
            Connection connection = jdbcTemplate.getDataSource().getConnection(); //获得数据库连接

            PreparedStatement preparedStatement = connection.prepareStatement(SELECT_BY_ID);  //创建预备语句
            preparedStatement.setLong(1, id);        //设置sql语句中的参数值
            ResultSet resultSet = preparedStatement.executeQuery();       //查询

            while (resultSet.next()) {       //循环遍历结果集
                long school_id = resultSet.getLong("schoolid");
                PreparedStatement preparedStatement1 = connection.prepareStatement(SELECT_BY_FSID);  //创建预备语句
                preparedStatement1.setLong(1, school_id);        //设置sql语句中的参数值
                ResultSet resultSet2 = preparedStatement1.executeQuery();
                FoodSchool result = new FoodSchool();
                result.setFoodSchoolID(resultSet2.getLong("foodSchoolID"));
                result.setUserid(resultSet2.getLong("userid"));
                result.setClassName(resultSet2.getString("className"));
                result.setClassDes(resultSet2.getString("classDes"));
                result.setClassImg(resultSet2.getString("classImg"));
                classList.add(result);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return classList;

    }

    //查用户选了什么课
    public List<FoodSchool> findClassByUserid(long userid) {
        List<FoodSchool> schoolList = new ArrayList<>();
        try {
            Connection connection = jdbcTemplate.getDataSource().getConnection(); //获得数据库连接

            PreparedStatement preparedStatement = connection.prepareStatement(COUNT);  //创建预备语句
            preparedStatement.setLong(1, userid);        //设置sql语句中的参数值
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                FoodSchool result = new FoodSchool();
                result.setFoodSchoolID(resultSet.getLong("foodSchoolID"));
                result.setUserid(resultSet.getLong("userid"));
                result.setClassName(resultSet.getString("className"));
                result.setClassDes(resultSet.getString("classDes"));
                result.setClassImg(resultSet.getString("classImg"));
                schoolList.add(result);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return schoolList;
    }

    //查找课程
    public FoodSchool findSchoolBySchoolname(String schoolname) {
        FoodSchool result = null;
        try {
            Connection connection = jdbcTemplate.getDataSource().getConnection(); //获得数据库连接
            PreparedStatement preparedStatement = connection.prepareStatement(SELECT_BY_NAME);  //创建预备语句
            preparedStatement.setString(1, schoolname);        //设置sql语句中的参数值
            ResultSet resultSet = preparedStatement.executeQuery();
            result = new FoodSchool();
            resultSet.next();
            result.setFoodSchoolID(resultSet.getLong("foodSchoolID"));
            result.setUserid(resultSet.getLong("userid"));
            result.setClassName(resultSet.getString("className"));
            result.setClassDes(resultSet.getString("classDes"));
            result.setClassImg(resultSet.getString("classImg"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    //改
    private static final String sql1 = "update foodschool set className=?,classDes=?,classImg=? where foodSchoolID=? ";

    public void updateClass(String className, String classDes, String classImg, long foodSchoolID) {
        Object args[] = new Object[]{className, classDes, classImg, foodSchoolID};
        int temp = jdbcTemplate.update(sql1, args);
        if (temp > 0) {
            System.out.println("更新成功");
        } else {
            System.out.println("更新失败");
        }
    }

    private static final String SELECT_FOODSCHOOL_BY_ID = "SELECT * FROM foodschool left join foodclass on foodschool.foodSchoolID=foodclass.foodschoolid where foodschool.foodSchoolID=?";

    /**
     * 返回课堂和其相关的课程信息
     *
     * @param schoolid
     * @return
     */
    public FoodSchool getSchoolInfo(long schoolid) {
        FoodSchool foodSchool = null;
        try {
            PreparedStatement preparedStatement = con.prepareStatement(SELECT_FOODSCHOOL_BY_ID);
            preparedStatement.setLong(1, schoolid);
            ResultSet resultSet = preparedStatement.executeQuery();

            boolean isFirst = true;       //联合查询，同一个课堂信息在多各结果集中出现

            foodSchool = new FoodSchool();
            List<FoodClass> foodClassList = new ArrayList<>();

            while (resultSet.next()) {
                if (isFirst) {                //只需要在第一个结果集中处理课堂信息
                    foodSchool.setFoodSchoolID(resultSet.getLong("foodSchoolID"));
                    foodSchool.setUserid(resultSet.getLong("userid"));
                    foodSchool.setClassName(resultSet.getString("className"));
                    foodSchool.setClassDes(resultSet.getString("classDes"));
                    foodSchool.setClassImg(resultSet.getString("classImg"));
                    isFirst = false;
                }
                FoodClass foodClass = new FoodClass();
                foodClass.setFoodClassid(resultSet.getLong("foodClassid"));
                foodClass.setFoodschoolid(resultSet.getLong("foodschoolid"));
                foodClass.setFoodClass_word(resultSet.getString("foodClass_word"));
                foodClass.setFoodClass_video(resultSet.getString("foodClass_video"));
                foodClass.setFoodClass_date(resultSet.getString("foodClass_date"));
                foodClass.setFoodClass_name(resultSet.getString("foodClass_name"));
                foodClass.setIsfree(resultSet.getBoolean("isfree"));
                foodClassList.add(foodClass);
            }
            foodSchool.setFoodClassList(foodClassList);
            return foodSchool;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

    }

    private final String GET_JOINED_COUNT = "select count(*) from joinschool where schoolid=?";

    public long getFoodSchoolJoinedCount(long schoolID) {
        try {
            return jdbcTemplate.queryForObject(GET_JOINED_COUNT, new RowMapper<Long>() {
                @Override
                public Long mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return rs.getLong(1);
                }
            }, schoolID);
        } catch (EmptyResultDataAccessException e) {
            return 0;
        }

    }
}
